package com.zxh.blog.util.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;

import java.awt.Color;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 写excel工具类
 * @Author: wb-zxh410423 子恕
 * @Description: 写excel工具类
 * @Date: Created in 16:09 2018/9/7
 */
public class ExcelWriter {


  /**
   * 导出excel
   * @param data
   * @param out
   * @param <T>
   * @throws Exception
   */
  public static <T> void exportExcel(List<T> data, OutputStream out, Class<T> tClass) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook();
    try {
//      String fileName = new SimpleDateFormat("yyyyMMdd").format(new Date()) + "_" + "sheet1" +".xls";
      XSSFSheet sheet = wb.createSheet("sheet1");
      writeExcel(wb, sheet, data, tClass);
      wb.write(out);
      out.flush();
    } finally {
      if(out != null) {
        out.close();
      }
    }
  }

  /**
   * 写表头和数据到excel
   * @param wb
   * @param sheet
   * @param data
   * @param tClass
   * @param <T>
   */
    private static <T> void writeExcel(XSSFWorkbook wb, Sheet sheet, List<T> data, Class<T> tClass) {

      ExcelData<T> excelData = new ExcelData(tClass, data);
      writeTitlesToExcel(wb, sheet, excelData.getHeaderRow());
      writeRowsToExcel(wb, sheet, excelData, tClass);
      autoSizeColumns(sheet, excelData.getHeaderRow().size() + 1);
    }

  /**
   * 写表头
   * @param wb
   * @param sheet
   * @param titles
   * @return
   */
  private static void writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
    int rowIndex = 0;
    int colIndex = 0;
    Font titleFont = wb.createFont();
    titleFont.setFontName("simsun");
    titleFont.setColor(IndexedColors.BLACK.index);

    XSSFCellStyle titleStyle = wb.createCellStyle();
    titleStyle.setAlignment(HorizontalAlignment.CENTER);
    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFont(titleFont);
    setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

    Row titleRow = sheet.createRow(rowIndex);

    for (String field : titles) {
      Cell cell = titleRow.createCell(colIndex);
      cell.setCellValue(field);
      cell.setCellStyle(titleStyle);
      colIndex++;
    }
  }

  /**
   * 写数据到excel
   * @param wb
   * @param sheet
   * @param excelData
   * @param tClass
   * @param <T>
   * @return
   */
  private static <T> void writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, ExcelData<T> excelData, Class<T> tClass) {
    int rowIndex = 1;
    //格式兼容不下来，
    List<List<String>> rows = excelData.getRows(tClass);
    for (List<String> rowData : rows) {
      Row row = sheet.createRow(rowIndex);
      writeCell(wb, rowData, row);
      rowIndex++;
    }
  }

  /**
   * 写单元格的数据
   * @param rowData
   * @param wb
   * @param row
   */
  private static void writeCell(XSSFWorkbook wb, List<String> rowData, Row row){
    Font dataFont = wb.createFont();
    dataFont.setFontName("simsun");
    dataFont.setColor(IndexedColors.BLACK.index);
    XSSFCellStyle dataStyle = wb.createCellStyle();
    dataStyle.setAlignment(HorizontalAlignment.CENTER);
    dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    dataStyle.setFont(dataFont);
    setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
    int colIndex = 0;
    for(String cellData : rowData) {
      Cell cell = row.createCell(colIndex);
      cell.setCellValue(cellData);
      cell.setCellStyle(dataStyle);
      colIndex++;
    }
  }

  /**
   * 设置size
   * @param sheet
   * @param columnNumber
   */
  private static void autoSizeColumns(Sheet sheet, int columnNumber) {

    for (int i = 0; i < columnNumber; i++) {
      int orgWidth = sheet.getColumnWidth(i);
      sheet.autoSizeColumn(i, true);
      int newWidth =  sheet.getColumnWidth(i) + 100;
      if (newWidth > orgWidth) {
        sheet.setColumnWidth(i, newWidth);
      } else {
        sheet.setColumnWidth(i, orgWidth);
      }
    }
  }

  /**
   * 设置单元格边框
   * @param style
   * @param border
   * @param color
   */
  private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
    style.setBorderTop(border);
    style.setBorderLeft(border);
    style.setBorderRight(border);
    style.setBorderBottom(border);
    style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
    style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
    style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
    style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
  }
}
